

 
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
public class LogHandler {
	public static String db = "";
	public static String pw = "";
	public static Connection conn = null;
	
	/**
	 * connDB - gets the connection information from the users.properties file
	 */
	public static void connDB(){
        Properties prop = new Properties();        
    	try {
            //load a properties file
    		prop.load(new FileInputStream("user.properties"));
    		db = prop.getProperty("db");
    		pw = prop.getProperty("pw");
    		
    	} catch (IOException ex) {
    		ex.printStackTrace();
        }
	}
	
	/**
	 * checkVisualization - checks to see if the Visualization information has already been inserted into the database. 
	 * @param Name - the name of the visualization
	 * @param IP - ip of user
	 * @param User - the username for the user
	 * @param ViewDate - the date the visualization was viewed
	 * @return boolean - false if nothing failed
	 */
	public static int checkVisualization(String Name, String IP, String User, Timestamp ViewDate) throws SQLException{
		
		PreparedStatement ps = null;

		//for junit testing
		boolean failed = false;
		int recordCount = 0;

		try{
			connDB();
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String  dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/" + db;
			//System.out.println(" Attempting Connections... ");
			conn = DriverManager.getConnection(dbURL,db,pw);
			//System.out.println(" Connection Successful! ");
			
		
			//SQL Statement
			String InsertSharedUser = "SELECT COUNT(*) FROM Visualization WHERE (name = ?) AND (ip = ?) AND (user = ?) AND (viewdate = ?)";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(InsertSharedUser);

			ResultSet rs = null;
			
			//Values for command
			ps.setString(1, Name);
			ps.setString(2, IP);
			ps.setString(3, User);
			ps.setTimestamp(4, ViewDate);			
			//System.out.println(" Checking Viz... ");
			rs = ps.executeQuery();
			rs.next(); 
			
			recordCount = rs.getInt(1);
			
			ps.close();
			conn.close();	
			}
		catch(Exception e) { e.printStackTrace(); failed = true;}
		return recordCount;
		}
	
	/**
	 * checkIP - checks to see if the IP information has already been inserted into the database. 
	 * @param IP - ip of user
	 * @param User - the username for the user
	 * @return boolean - false if nothing failed
	 */
	public static int checkIP(String IP, String User) throws SQLException{
		
		PreparedStatement ps = null;

		//for junit testing
		boolean failed = false;
		int recordCount = 0;

		try{
			connDB();
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String  dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/" + db;
			//System.out.println(" Attempting Connections... ");
			conn = DriverManager.getConnection(dbURL,db,pw);
			//System.out.println(" Connection Successful! ");
			
		
			//SQL Statement
			String InsertSharedUser = "SELECT COUNT(*) FROM IP WHERE (ip = ?) AND (user = ?)";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(InsertSharedUser);

			ResultSet rs = null;
			
			//Values for command
			ps.setString(1, IP);
			ps.setString(2, User);			
			//System.out.println(" Checking IP/User... ");
			rs = ps.executeQuery();
			rs.next(); 
			
			recordCount = rs.getInt(1);
			
			ps.close();
			conn.close();	
			}
		catch(Exception e) { e.printStackTrace(); failed = true;}
		return recordCount;
		}
	
	/**
	 * insertIP - inserts a record into the database with the user information
	 * @param IP - ip of user
	 * @param User - the username for the user
	 * @param Region - the region returned using the IP
	 * @param City - the city returned using the IP
	 * @param Country - the country returned using the IP
	 * @return boolean - false if nothing failed
	 */
	public static boolean insertIP(String IP, String User, String Region, String City, String Country) throws SQLException{
	
		PreparedStatement ps = null;

		//for junit testing
		boolean failed = false;

		if (checkIP(IP, User) < 1){
		try{
			connDB();
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String  dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/" + db;
			//System.out.println(" Attempting Connections... ");
			conn = DriverManager.getConnection(dbURL,db,pw);
			//System.out.println(" Connection Successful! ");			
			
		
			//SQL Statement
			String InsertSharedUser = "INSERT INTO IP(ip, user, region, city, country) VALUES(?,?,?,?,?)";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(InsertSharedUser);

			
			//Values for command
			ps.setString(1, IP);
			ps.setString(2, User);
			ps.setString(3, Region);
			ps.setString(4, City);
			ps.setString(5, Country);
			//System.out.println(" Attempting Insert... ");
			ps.executeUpdate();
			conn.commit();
			//System.out.println(" Insert Successful! ");
			ps.close();
			conn.close();	
			}
		catch(Exception e) { e.printStackTrace(); failed = true;}
		}
		else{
			//System.out.println(" IP/User Already Exists ");
		}
		return failed;
	}
	
	/**
	 * insertVisualization - inserts a record into the database with the visualization information 
	 * @param Name - the name of the visualization
	 * @param IP - ip of user
	 * @param User - the username for the user
	 * @param ViewDate - the date the visualization was viewed
	 * @return boolean - false if nothing failed
	 */
	public static boolean insertVisualization(String Name, String IP, String User, Timestamp ViewDate) throws SQLException{
		
		PreparedStatement ps = null;

		//for junit testing
		boolean failed = false;

		
		if (checkVisualization(Name, IP, User, ViewDate) < 1){
		try{
			connDB();
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String  dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/" + db;
			//System.out.println(" Attempting Connections... ");
			conn = DriverManager.getConnection(dbURL,db,pw);
			//System.out.println(" Connection Successful! ");
			
		
			//SQL Statement
			String InsertSharedUser = "INSERT INTO Visualization(name, ip, user, viewdate) VALUES(?,?,?,?)";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(InsertSharedUser);

			
			//Values for command
			ps.setString(1, Name);
			ps.setString(2, IP);
			ps.setString(3, User);
			ps.setTimestamp(4, ViewDate);			
			//System.out.println(" Attempting Insert... ");
			ps.executeUpdate();
			conn.commit();
			//System.out.println(" Insert Successful! ");
			ps.close();
			conn.close();
			}
		catch(Exception e) { e.printStackTrace(); failed = true;}
	}
	else{
		//System.out.println(" Viz Already Exists ");
	}
		return failed;
		}
	
	
	/**
	 * deleteIP - deletes a record from the database using the user information
	 * @param IP - ip of user
	 * @param User - the username for the user
	 * @return boolean - false if nothing failed
	 */
	public static boolean deleteIP(String IP, String User) throws SQLException{
	
		PreparedStatement ps = null;

		//for junit testing
		boolean failed = false;

		try{
			connDB();
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String  dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/" + db;
			//System.out.println(" Attempting Connections... ");
			conn = DriverManager.getConnection(dbURL,db,pw);
			//System.out.println(" Connection Successful! ");
			
		
			//SQL Statement
			String InsertSharedUser = "DELETE FROM IP WHERE (ip = ?) AND (user = ?)";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(InsertSharedUser);

			
			//Values for command
			ps.setString(1, IP);
			ps.setString(2, User);
			//System.out.println(" Attempting Delete... ");
			ps.executeUpdate();
			conn.commit();
			//System.out.println(" Delete Successful! ");
			ps.close();
			conn.close();	
			}
		catch(Exception e) { e.printStackTrace(); failed = true;}
		return failed;
		}
	
	
	/**
	 * deleteVisualization - deletes a record from the database using the visualization information 
	 * @param Name - the name of the visualization
	 * @param IP - ip of user
	 * @param User - the username for the user
	 * @param ViewDate - the date the visualization was viewed
	 * @return boolean - false if nothing failed
	 */
	public static boolean deleteVisualization(String Name, String IP, String User, Timestamp ViewDate) throws SQLException{
		
		PreparedStatement ps = null;

		//for junit testing
		boolean failed = false;

		try{
			connDB();
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			String  dbURL = "jdbc:mysql://cs346.cs.uwosh.edu:4381/" + db;
			//System.out.println(" Attempting Connections... ");
			conn = DriverManager.getConnection(dbURL,db,pw);
			//System.out.println(" Connection Successful! ");
			
		
			//SQL Statement
			String InsertSharedUser = "DELETE FROM Visualization WHERE (name = ?) AND (ip = ?) AND (user = ?) AND (viewdate = ?)";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(InsertSharedUser);

			
			//Values for command
			ps.setString(1, Name);
			ps.setString(2, IP);
			ps.setString(3, User);
			ps.setTimestamp(4, ViewDate);			
			//System.out.println(" Attempting Delete... ");
			ps.executeUpdate();
			conn.commit();
			//System.out.println(" Delete Successful! ");
			ps.close();
			conn.close();	
			}
		catch(Exception e) { e.printStackTrace(); failed = true;}
		return failed;
		}

}

